﻿
GO

/****** Object:  Table [dbo].[Theme]    Script Date: 02/12/2012 20:44:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Theme](
	[Id] [int] IDENTITY(1,1) NOT NULL ,
	[Name] [varchar](100) NOT NULL,
	[FontColor] [varchar](6) NOT NULL,
	[ButtonImageUrl] [varchar](100) NOT NULL,
	[MenuBackgroundImageUrl] [varchar](100) NOT NULL,
	[CreatedBy] [varchar](250) NULL,
	[UpdatedBy] [varchar](250) NULL,
	[CreatedDate] [datetime] NULL,
	[UpdatedDate] [datetime] NULL,
	[IsActive] [bit] default(0) NOT NULL,
	[IsDeleted] [bit] default(0) NOT NULL,
 CONSTRAINT [PK_Theme] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO










INSERT INTO [dbo].[Theme]
           ([Name]
           ,[FontColor]
           ,[ButtonImageUrl]
           ,[MenuBackgroundImageUrl]
           ,[IsActive]
           ,[IsDeleted])
     VALUES
           ('Default Blue Theme'
           ,'FFFFFF'
           ,'/Images/Buttons/button.gif'
           ,'/Images/Navigation/bg.gif'
           ,1
           ,0)
GO




ALTER TABLE LogoInformation
Add ThemeId int

ALTER TABLE LogoInformation
ADD FOREIGN KEY (ThemeId)
REFERENCES Theme(Id)

GO


GO
/****** Object:  StoredProcedure [dbo].[GetAllThemes]    Script Date: 02/12/2012 21:00:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Feb 12, 2012
-- Description:	<Description,,>
--exec [GetAllThemes]
-- =============================================
CREATE PROCEDURE [dbo].[GetAllThemes]

AS
BEGIN
	
	SELECT * FROM Theme t
	WHERE t.IsActive = 1 and t.IsDeleted  =0

END



GO



/****** Object:  StoredProcedure [dbo].[SP_LogoInformationUpdate]    Script Date: 02/13/2012 09:28:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  -- =============================================  
-- Author:  <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
ALTER PROCEDURE [dbo].[SP_LogoInformationUpdate]  
 @CurrentDate Datetime  
           ,@FarmanImage varchar(500)  
           ,@HeaderImage varchar(500)  
           ,@LogoImage varchar(500)  
           ,@UpdatedBy varchar(100)  
             
           ,@UpdatedDate datetime  
           ,@Id int  
           ,@FromDate datetime
           ,@Url varchar(250)
           ,@ThemeId int
AS  
BEGIN  
Delete from logodetailDetail  where LogoDetailId in ( select id from logodetail ld where ld.LogoInformationId=@Id)  
Delete from logodetail  where LogoInformationId=@Id  
  
 UPDATE [dbo].[LogoInformation]  
   SET [CurrentDate] = @CurrentDate  
      ,[FarmanImage] = @FarmanImage  
      ,[HeaderImage] = @HeaderImage  
      ,[LogoImage] = @LogoImage  
      ,FromDate = @FromDate
      ,Url = @Url
      ,ThemeId = @ThemeId
      ,[UpdatedBy] = @UpdatedBy  
        
      ,[UpdatedDate] = @UpdatedDate  
 WHERE Id= @Id  
  
  
  
  
END  
  

  GO



GO
/****** Object:  StoredProcedure [dbo].[SP_LogInformationInsert]    Script Date: 02/13/2012 09:30:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================  
-- Author:  <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
ALTER PROCEDURE [dbo].[SP_LogInformationInsert]  
@Id int OUTPUT,  
 @CurrentDate Datetime  
           ,@FarmanImage varchar(500)  
           ,@HeaderImage varchar(500)  
           ,@LogoImage varchar(500)  
           ,@CreatedBy varchar(100)  
            
           ,@CreatedDate datetime  
           ,@FromDate datetime,
            @Url varchar(250)
            ,@ThemeId int
AS  
BEGIN  
 INSERT INTO [dbo].[LogoInformation]  
           ([CurrentDate]  
           ,[FarmanImage]  
           ,[HeaderImage]  
           ,[LogoImage]  
           ,[CreatedBy]   
           ,[CreatedDate]  
		   ,FromDate
           ,Url
           ,ThemeId
          )  
     VALUES  
           (@CurrentDate  
           ,@FarmanImage  
           ,@HeaderImage  
           ,@LogoImage  
           ,@CreatedBy  
           ,@CreatedDate
		   ,@FromDate
           ,@Url
           ,@ThemeId
           )  
             
            SET @Id = SCOPE_IDENTITY()  
END  
  



GO



GO
/****** Object:  StoredProcedure [dbo].[SP_LogoInformation]    Script Date: 02/13/2012 09:46:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec [SP_LogoInformation] null
ALTER PROCEDURE [dbo].[SP_LogoInformation]     
 @Url varchar(150),
 @IsPreview BIT = 0    
AS    
BEGIN    
     
 DECLARE @LogoId INT;    
    
 SELECT TOP 1 @LogoId = li.id     
 FROM LogoInformation li     
 WHERE     
  li.Url= case when @Url = '' then li.Url else ISNULL(@Url,li.Url) end 
  AND (li.isDeleted is null OR li.isDeleted = 0 )    
  AND IsActive = CASE WHEN @IsPreview = 0 THEN 1 ELSE IsActive END    
 ORDER BY ID DESC    
     
 print (@LogoId);     
    
 IF(@LogoId > 0)    
 BEGIN    
    
 select * from LogoInformation li    
 where li.id = @LogoId;    
    
 Select tl.Description as MenuName , d.* from LogoDetail d inner join tblLookup tl on tl.LookUpID=d.MenuId     
 where d.LogoInformationId in (select li.Id from LogoInformation li where li.id = @LogoId)    
    
 SELECT * FROM LogoDetailDetail ddl     
   
 WHERE ddl.LogoDetailId IN     
 (    
  SELECT ld.Id FROM LogoDetail ld     
  INNER JOIN tblLookup tl     
  ON tl.LookUpID=ld.MenuId     
  WHERE ld.LogoInformationId IN     
  (    
   SELECT li.Id FROM LogoInformation li    
   where li.id = @LogoId    
  )  
     
  --SELECT li.Id FROM LogoInformation li    
  --WHERE li.CurrentDate=@CurrentDate    
 )  
 order by ddl.OrderNo asc     
 
 
 
 SELECT * FROM Theme WHERE Id = (SELECT ThemeId FROM LogoInformation WHERE Id = @LogoId)
 
 
 END    
     
END    
    
    
    
    
GO


update LogoInformation set ThemeId = 1 where ThemeId is null
    


GO


alter table tblLookup 
alter column ExtraData nvarchar(1000)



GO


alter table theme
add HomeIconUrl varchar(250) null
GO
alter table theme
add ContactUsIconUrl varchar(250) null
GO
alter table theme
add CurrentMagazineIconUrl varchar(250) null



GO


update Theme
set 
	HomeIconUrl = '/Images/Navigation/Home.gif'
	,ContactUsIconUrl = '/Images/Navigation/contactus.gif'
	,CurrentMagazineIconUrl = '/Images/Buttons/ClickHere.gif'
where Id = 1

GO


UPDATE dbo.tblLookup SET ExtraData = N'دین و دنیا' WHERE LookUpID = 1
UPDATE dbo.tblLookup SET ExtraData = N'شہر کہانی' WHERE LookUpID = 2
UPDATE dbo.tblLookup SET ExtraData = N'نام گرامی' WHERE LookUpID = 3
UPDATE dbo.tblLookup SET ExtraData = N'کھیل کھلاڑی' WHERE LookUpID = 4
UPDATE dbo.tblLookup SET ExtraData = N'حرمت قلم' WHERE LookUpID = 5
UPDATE dbo.tblLookup SET ExtraData = N'شہیدِ وطن' WHERE LookUpID = 6
UPDATE dbo.tblLookup SET ExtraData = N'صحت و تندرستی' WHERE LookUpID = 7
UPDATE dbo.tblLookup SET ExtraData = N'لذت و توانائی' WHERE LookUpID = 8
UPDATE dbo.tblLookup SET ExtraData = N'پردیس جانے والے' WHERE LookUpID = 9
UPDATE dbo.tblLookup SET ExtraData = N'ازدواجی حقوق' WHERE LookUpID = 10
UPDATE dbo.tblLookup SET ExtraData = N'یومِ یکجہتی' WHERE LookUpID = 12
UPDATE dbo.tblLookup SET ExtraData = N'صدی کا شاعر' WHERE LookUpID = 13
UPDATE dbo.tblLookup SET ExtraData = N'اندازِ غالب' WHERE LookUpID = 14
UPDATE dbo.tblLookup SET ExtraData = N'نیٹ ورکنگ' WHERE LookUpID = 15
UPDATE dbo.tblLookup SET ExtraData = N'موبائل کی اہمیت' WHERE LookUpID = 16
UPDATE dbo.tblLookup SET ExtraData = N'سرِ بزم' WHERE LookUpID = 17